﻿DECLARE @INTMONTH INT = 1
DECLARE @SMONTH NVARCHAR(20)
DECLARE @SQL NVARCHAR(MAX)
DECLARE @DATE_UPDATE NVARCHAR(100)= (SELECT [DATE_UPDATE] FROM [DATE_EM_REPORT])
DECLARE @MONTH NVARCHAR(100)=(SELECT [MAX_MONTH] FROM [DATE_EM_REPORT])
DECLARE @DATE_END_MONTH NVARCHAR(100)= (SELECT [MAX_DATE_ENDMONTH] FROM [DATE_EM_REPORT])
DECLARE @NOWMONTH INT = MONTH(CONVERT(DATE,@DATE_END_MONTH))
DECLARE @MYDATE DATE = (CONVERT(DATE,@DATE_END_MONTH))
DECLARE @DATE_BEGIN_MONTH NVARCHAR(100)= CONVERT(VARCHAR(25),DATEADD(D,-(DAY(@MYDATE)-1),@MYDATE),112)
declare @sMonths nvarchar(20)
--print (@DATE_END_MONTH)
--print (@DATE_BEGIN_MONTH)

PRINT('1.CREATE DATA LIMIT')

--set @NOWMONTH = 7
--set @DATE_BEGIN_MONTH = '20160701'
--set @DATE_END_MONTH = '20160730'
	SET @SMONTH= CAST(@NOWMONTH AS NVARCHAR(20))
--PRINT (@SMONTH)
exec('
IF not EXISTS
      (SELECT *
         FROM DBO.SYSOBJECTS O
        WHERE O.XTYPE IN (''U'') AND O.NAME = ''TBL_LIMIT_ORG_MONTH_'+@SMONTH+''')
SELECT * INTO TBL_LIMIT_ORG_MONTH_'+@SMONTH+' FROM [SERVER16].[VPB_WHR2].[DBO].[TBL_LIMIT_ORG] WHERE BUSINESS_DATE = '''+@DATE_END_MONTH+'''

		')
--		declare @NOWMONTH int = 8
--		DECLARE @INTMONTH INT = 1
--DECLARE @SMONTH NVARCHAR(20)
--DECLARE @SQL NVARCHAR(MAX)
	DELETE FROM TBL_LIMIT_ORG_ACCOUNT_PLANNING_MONTH
WHILE @INTMONTH <=@NOWMONTH
BEGIN
	SET @SMONTH= CAST(@INTMONTH AS NVARCHAR(20))
	SET @SQL='
	INSERT INTO TBL_LIMIT_ORG_ACCOUNT_PLANNING_MONTH
	SELECT *,(CASE WHEN INTERNAL_AMOUNT LIKE''%B%''THEN REPLACE(INTERNAL_AMOUNT,''B'',''000000000'')
		WHEN INTERNAL_AMOUNT LIKE''%M%''THEN REPLACE(INTERNAL_AMOUNT,''M'',''000000'') 
		ELSE INTERNAL_AMOUNT END)AS INTENRNAL_AMOUNT_QD, '+@SMONTH+' AS YEARMONTH
	FROM TBL_LIMIT_ORG_MONTH_'+@SMONTH+'
	WHERE INTERNAL_AMOUNT IS NOT NULL
	AND LIABILITY_NUMBER IN (SELECT CIF FROM TBL_ACCOUNT_PLANNING)'
	EXEC(@SQL)
	 SET @INTMONTH = @INTMONTH + 1
END


IF OBJECT_ID('TEMPDB.DBO.#LIMIT_SUM_CIF') IS NOT NULL  
  DROP TABLE #LIMIT_SUM_CIF 

	SELECT SUM(CONVERT(FLOAT, INTENRNAL_AMOUNT_QD)) AS LIMIT_CIF, LIABILITY_NUMBER, YEARMONTH INTO #LIMIT_SUM_CIF
		FROM TBL_LIMIT_ORG_ACCOUNT_PLANNING_MONTH 
		WHERE  ID LIKE '%0050000%'
		GROUP BY LIABILITY_NUMBER, YEARMONTH

IF OBJECT_ID('TEMPDB.DBO.#LIMIT_VAYVON_SUM_CIF') IS NOT NULL
  DROP TABLE #LIMIT_VAYVON_SUM_CIF 

	SELECT SUM(CONVERT(FLOAT, INTENRNAL_AMOUNT_QD)) AS LIMIT_CIF, LIABILITY_NUMBER, YEARMONTH INTO #LIMIT_VAYVON_SUM_CIF
			FROM TBL_LIMIT_ORG_ACCOUNT_PLANNING_MONTH 
			WHERE  ID LIKE '%0058100%'
			GROUP BY LIABILITY_NUMBER, YEARMONTH

IF OBJECT_ID('TEMPDB.DBO.#LIMIT_LC_SUM_CIF') IS NOT NULL
  DROP TABLE #LIMIT_LC_SUM_CIF 

	SELECT SUM(CONVERT(FLOAT, INTENRNAL_AMOUNT_QD)) AS LIMIT_CIF, LIABILITY_NUMBER, YEARMONTH INTO #LIMIT_LC_SUM_CIF
			FROM TBL_LIMIT_ORG_ACCOUNT_PLANNING_MONTH
			WHERE  ID LIKE '%0058300%' 
			GROUP BY LIABILITY_NUMBER, YEARMONTH

IF OBJECT_ID('TEMPDB.DBO.#LIMIT_BL_SUM_CIF') IS NOT NULL
  DROP TABLE #LIMIT_BL_SUM_CIF 

	 SELECT SUM(CONVERT(FLOAT, INTENRNAL_AMOUNT_QD)) AS LIMIT_CIF, LIABILITY_NUMBER, YEARMONTH INTO #LIMIT_BL_SUM_CIF
			FROM TBL_LIMIT_ORG_ACCOUNT_PLANNING_MONTH 
			WHERE  ID LIKE '%0058200%' 
			GROUP BY LIABILITY_NUMBER, YEARMONTH

PRINT('UPDATE LIMIT')
--DECLARE @INTMONTH INT
--SET @INTMONTH = 1
--DECLARE @SMONTH NVARCHAR(20)
--DECLARE @SQL NVARCHAR(MAX)
SET @INTMONTH = 1
WHILE @INTMONTH <=@NOWMONTH
BEGIN
	SET @SMONTH= CAST(@INTMONTH AS NVARCHAR(20))
	SET @SQL='
	UPDATE TBL_ACCOUNT_PLANNING_FINANCE_RESULT
	SET MONTH'+@SMONTH+' = STR(B.LIMIT_CIF, 25,0)
	FROM TBL_ACCOUNT_PLANNING_FINANCE_RESULT AS A, #LIMIT_SUM_CIF AS B
	WHERE A.CIF=B.LIABILITY_NUMBER AND B.YEARMONTH='''+@SMONTH+''' AND ORDER_NUM = ''23'''
	EXEC(@SQL)

	SET @SQL='
	UPDATE TBL_ACCOUNT_PLANNING_FINANCE_RESULT
	SET MONTH'+@SMONTH+' = STR(B.LIMIT_CIF, 25,0)
	FROM TBL_ACCOUNT_PLANNING_FINANCE_RESULT AS A, #LIMIT_VAYVON_SUM_CIF AS B
	WHERE A.CIF=B.LIABILITY_NUMBER AND B.YEARMONTH='''+@SMONTH+''' AND ORDER_NUM = ''24'''
	EXEC(@SQL)
	
	SET @SQL='
	UPDATE TBL_ACCOUNT_PLANNING_FINANCE_RESULT
	SET MONTH'+@SMONTH+' = STR(B.LIMIT_CIF, 25,0)
	FROM TBL_ACCOUNT_PLANNING_FINANCE_RESULT AS A, #LIMIT_LC_SUM_CIF AS B
	WHERE A.CIF=B.LIABILITY_NUMBER AND B.YEARMONTH='''+@SMONTH+''' AND ORDER_NUM = ''25'''
	EXEC(@SQL)

	SET @SQL='
	UPDATE TBL_ACCOUNT_PLANNING_FINANCE_RESULT
	SET MONTH'+@SMONTH+' = STR(B.LIMIT_CIF, 25,0)
	FROM TBL_ACCOUNT_PLANNING_FINANCE_RESULT AS A, #LIMIT_BL_SUM_CIF AS B
	WHERE A.CIF=B.LIABILITY_NUMBER AND B.YEARMONTH='''+@SMONTH+''' AND ORDER_NUM = ''26'''
	EXEC(@SQL)
	SET @INTMONTH = @INTMONTH + 1
END

PRINT('2.CREATE DATA QUY MO')
DELETE FROM BALANCE_ENDMONTH_ACCOUNT_PLANNING
DELETE FROM AVGBAL2016

--SELECT DISTINCT APP FROM BALANCE_ENDMONTH_1

--DECLARE @DATE_END_MONTH NVARCHAR(100)= (SELECT [MAX_DATE_ENDMONTH] FROM [DATE_EM_REPORT])
--DECLARE @MONTH NVARCHAR(100)=(SELECT [MAX_MONTH] FROM [DATE_EM_REPORT])
--DECLARE @NOWMONTH INT = MONTH(CONVERT(DATE,@DATE_END_MONTH))
--	DECLARE @SMONTH nvarchar(20)= CAST(7 AS NVARCHAR(20))
--set @NOWMONTH = 7
--set @DATE_END_MONTH = '20160730'
--SET @MONTH= '201607'
	SET @SMONTH= CAST(@NOWMONTH AS NVARCHAR(20))
exec('
IF EXISTS
      (SELECT *
         FROM DBO.SYSOBJECTS O
        WHERE O.XTYPE IN (''U'') AND O.NAME = ''BALANCE_ENDMONTH_'+@SMONTH+''')
        DROP TABLE BALANCE_ENDMONTH_'+@SMONTH+'

SELECT * INTO BALANCE_ENDMONTH_'+@SMONTH+' FROM [SERVER74].[BICDATA].[dbo].[BALANCE_ENDMONTH] WHERE YEARMONTH = '''+@MONTH+'''
		')
		delete from BALANCE_ENDMONTH_ACCOUNT_PLANNING
		delete from AvgBal2016
SET @INTMONTH = 1
WHILE @INTMONTH <=@NOWMONTH
BEGIN
	SET @SMONTH= CAST(@INTMONTH AS NVARCHAR(20))

	SET @SQL='
	INSERT INTO [BALANCE_ENDMONTH_ACCOUNT_PLANNING]
	SELECT YEARMONTH, APP, PRODUCT_NAME, BAL_QD, CIF, OPENNING_DATE, ISSUE_DATE 
	FROM [BALANCE_ENDMONTH_'+@SMONTH+'] 
	WHERE CIF collate database_default IN (SELECT CIF FROM TBL_ACCOUNT_PLANNING)'

	if(@INTMONTH <10) set @SMONTH = '0' + @SMONTH
	
	EXEC(@SQL)
	SET @SQL='
	INSERT INTO AVGBAL2016
	SELECT *, YEARMONTH=''2016'+@SMONTH+''' 
	FROM [ANHCP].[CUONGDB].[DBO].[AVGBAL2016'+@SMONTH+']
	WHERE CIF collate database_default IN (SELECT CIF FROM TBL_ACCOUNT_PLANNING)'
	EXEC(@SQL)
	SET @INTMONTH = @INTMONTH + 1
END
IF OBJECT_ID('TEMPDB.DBO.#BALANCE_DEPOSIT_SUM') IS NOT NULL
  DROP TABLE #BALANCE_DEPOSIT_SUM 
SELECT SUM(CONVERT(FLOAT, BAL_QD)) AS BAL_QD_SUM,YEARMONTH, CIF  INTO #BALANCE_DEPOSIT_SUM
		FROM [BALANCE_ENDMONTH_ACCOUNT_PLANNING] 
		WHERE APP='DEPOSIT'
		GROUP BY CIF, YEARMONTH

IF OBJECT_ID('TEMPDB.DBO.#BALANCE_DEPOSIT_CASA_SUM') IS NOT NULL
  DROP TABLE #BALANCE_DEPOSIT_CASA_SUM 
SELECT SUM(CONVERT(FLOAT, BAL_QD)) AS BAL_QD_SUM,YEARMONTH, CIF  INTO #BALANCE_DEPOSIT_CASA_SUM
		FROM [BALANCE_ENDMONTH_ACCOUNT_PLANNING] 
		WHERE APP='DEPOSIT' AND PRODUCT_NAME <> '2. TERM DEPOSIT'
		GROUP BY CIF, YEARMONTH

IF OBJECT_ID('TEMPDB.DBO.#BALANCE_DEPOSIT_KY_HAN_SUM') IS NOT NULL
  DROP TABLE #BALANCE_DEPOSIT_KY_HAN_SUM 
SELECT SUM(CONVERT(FLOAT, BAL_QD)) AS BAL_QD_SUM,YEARMONTH, CIF  INTO #BALANCE_DEPOSIT_KY_HAN_SUM
		FROM [BALANCE_ENDMONTH_ACCOUNT_PLANNING] 
		WHERE APP='DEPOSIT' AND PRODUCT_NAME = '2. TERM DEPOSIT'
		GROUP BY CIF, YEARMONTH

IF OBJECT_ID('TEMPDB.DBO.#AVG_DEPOSIT_SUM_AP') IS NOT NULL
  DROP TABLE #AVG_DEPOSIT_SUM_AP 
SELECT SUM(CONVERT(FLOAT, AVGBAL_QD)) AS BAL_QD_SUM,YEARMONTH, CIF  INTO #AVG_DEPOSIT_SUM_AP
		FROM AVGBAL2016 
		WHERE APP='DEPOSIT'
		GROUP BY CIF, YEARMONTH

IF OBJECT_ID('TEMPDB.DBO.#AVG_DEPOSIT_CASA_SUM_AP') IS NOT NULL
  DROP TABLE #AVG_DEPOSIT_CASA_SUM_AP 
SELECT SUM(CONVERT(FLOAT, AVGBAL_QD)) AS BAL_QD_SUM,YEARMONTH, CIF  INTO #AVG_DEPOSIT_CASA_SUM_AP
		FROM AVGBAL2016 
		WHERE APP='DEPOSIT' AND PRODUCT_GROUP <> '2. TERM DEPOSIT'
		GROUP BY CIF, YEARMONTH

IF OBJECT_ID('TEMPDB.DBO.#AVG_DEPOSIT_KY_HAN_SUM_AP') IS NOT NULL
  DROP TABLE #AVG_DEPOSIT_KY_HAN_SUM_AP 
SELECT SUM(CONVERT(FLOAT, AVGBAL_QD)) AS BAL_QD_SUM,YEARMONTH, CIF  INTO #AVG_DEPOSIT_KY_HAN_SUM_AP
		FROM AVGBAL2016 
		WHERE APP='DEPOSIT' AND PRODUCT_GROUP = '2. TERM DEPOSIT'
		GROUP BY CIF, YEARMONTH

IF OBJECT_ID('TEMPDB.DBO.#AVG_LOAN_SUM_AP') IS NOT NULL
  DROP TABLE #AVG_LOAN_SUM_AP 
SELECT SUM(CONVERT(FLOAT, AVGBAL_QD)) AS BAL_QD_SUM,YEARMONTH, CIF  INTO #AVG_LOAN_SUM_AP
		FROM AVGBAL2016 
		WHERE APP='LOAN'
		GROUP BY CIF, YEARMONTH

IF OBJECT_ID('TEMPDB.DBO.#AVG_LOAN_SECURED_AP') IS NOT NULL
  DROP TABLE #AVG_LOAN_SECURED_AP 
SELECT SUM(CONVERT(FLOAT, AVGBAL_QD)) AS BAL_QD_SUM,YEARMONTH, CIF  INTO #AVG_LOAN_SECURED_AP
		FROM AVGBAL2016 
		WHERE APP='LOAN' AND CHUONG_TRINH_SP NOT LIKE '%BIL%' AND  CHUONG_TRINH_SP NOT LIKE '%LOAN%'
		GROUP BY CIF, YEARMONTH

IF OBJECT_ID('TEMPDB.DBO.#AVG_LOAN_UNSECURED_AP') IS NOT NULL
  DROP TABLE #AVG_LOAN_UNSECURED_AP 
SELECT SUM(CONVERT(FLOAT, AVGBAL_QD)) AS BAL_QD_SUM,YEARMONTH, CIF  INTO #AVG_LOAN_UNSECURED_AP
		FROM AVGBAL2016 
		WHERE APP='LOAN' AND (CHUONG_TRINH_SP LIKE '%BIL%' OR  CHUONG_TRINH_SP LIKE '%LOAN%')
		GROUP BY CIF, YEARMONTH

DELETE FROM SAOKE_LUYKE_GIANGLH3

INSERT INTO SAOKE_LUYKE_GIANGLH3
SELECT * FROM [GIANGLH3].[RISK].[DBO].[SAOKE_LUYKE]
--SELECT * FROM SAOKE_LUYKE_GIANGLH3
IF OBJECT_ID('TEMPDB.DBO.#SAOKE_LUYKE_TAM') IS NOT NULL
DROP TABLE #SAOKE_LUYKE_TAM
SELECT SUM(CONVERT(FLOAT, [SO DU])) AS SUM_SO_DU, CUSTID AS CIF, MONTH AS YEARMONTH INTO #SAOKE_LUYKE_TAM
FROM SAOKE_LUYKE_GIANGLH3 AS A WHERE CUSTID IN (SELECT CIF FROM TBL_ACCOUNT_PLANNING) AND MONTH > '201512' 
GROUP BY CUSTID, MONTH
ORDER BY CUSTID

IF OBJECT_ID('TEMPDB.DBO.#SAOKE_LUYKE_SECURED') IS NOT NULL
DROP TABLE #SAOKE_LUYKE_SECURED
SELECT SUM(CONVERT(FLOAT, [SO DU])) AS SUM_SO_DU, CUSTID AS CIF, MONTH AS YEARMONTH INTO #SAOKE_LUYKE_SECURED
FROM SAOKE_LUYKE_GIANGLH3 AS A 
WHERE CUSTID IN (SELECT CIF FROM TBL_ACCOUNT_PLANNING) AND MONTH > '201512' AND SANPHAM_GROUP <> '4.UNSECURED'
GROUP BY CUSTID, MONTH
ORDER BY CUSTID

IF OBJECT_ID('TEMPDB.DBO.#SAOKE_LUYKE_UNSECURED') IS NOT NULL
DROP TABLE #SAOKE_LUYKE_UNSECURED
SELECT SUM(CONVERT(FLOAT, [SO DU])) AS SUM_SO_DU, CUSTID AS CIF, MONTH AS YEARMONTH INTO #SAOKE_LUYKE_UNSECURED
FROM SAOKE_LUYKE_GIANGLH3 AS A 
WHERE CUSTID IN (SELECT CIF FROM TBL_ACCOUNT_PLANNING) AND MONTH > '201512' AND SANPHAM_GROUP = '4.UNSECURED'
GROUP BY CUSTID, MONTH
ORDER BY CUSTID


PRINT('UPDATE QUY MO')
--DECLARE @INTMONTH INT
--SET @INTMONTH = 1
--DECLARE @SMONTH NVARCHAR(20)
--DECLARE @SQL NVARCHAR(MAX)
SET @INTMONTH = 1
WHILE @INTMONTH <=@NOWMONTH
BEGIN
	SET @SMONTH= CAST(@INTMONTH AS NVARCHAR(20))

	set @sMonths = @sMonth

	if(@INTMONTH <10) set @SMONTH = '0' + @SMONTH

	SET @SQL='
	UPDATE TBL_ACCOUNT_PLANNING_FINANCE_RESULT
	SET MONTH'+@sMonths+' = STR(B.SUM_SO_DU, 25,0)
	FROM TBL_ACCOUNT_PLANNING_FINANCE_RESULT AS A, #SAOKE_LUYKE_TAM AS B
	WHERE A.CIF=B.CIF AND B.YEARMONTH=''2016'+@SMONTH+''' AND ORDER_NUM = ''1'''
	EXEC(@SQL)
	SET @SQL='
	UPDATE TBL_ACCOUNT_PLANNING_FINANCE_RESULT
	SET MONTH'+@sMonths+' = STR(B.SUM_SO_DU, 25,0)
	FROM TBL_ACCOUNT_PLANNING_FINANCE_RESULT AS A, #SAOKE_LUYKE_SECURED AS B
	WHERE A.CIF=B.CIF AND B.YEARMONTH=''2016'+@SMONTH+''' AND ORDER_NUM = ''2'''
	EXEC(@SQL)
	SET @SQL='
	UPDATE TBL_ACCOUNT_PLANNING_FINANCE_RESULT
	SET MONTH'+@sMonths+' = STR(B.SUM_SO_DU, 25,0)
	FROM TBL_ACCOUNT_PLANNING_FINANCE_RESULT AS A, #SAOKE_LUYKE_UNSECURED AS B
	WHERE A.CIF=B.CIF AND B.YEARMONTH=''2016'+@SMONTH+''' AND ORDER_NUM = ''3'''
	EXEC(@SQL)

	SET @SQL='
	UPDATE TBL_ACCOUNT_PLANNING_FINANCE_RESULT
	SET MONTH'+@sMonths+' = STR(B.BAL_QD_SUM, 25,0)
	FROM TBL_ACCOUNT_PLANNING_FINANCE_RESULT AS A, #AVG_LOAN_SUM_AP AS B
	WHERE A.CIF=B.CIF AND B.YEARMONTH=''2016'+@SMONTH+''' AND ORDER_NUM = ''4'''
	EXEC(@SQL)
	SET @SQL='
	UPDATE TBL_ACCOUNT_PLANNING_FINANCE_RESULT
	SET MONTH'+@sMonths+' = STR(B.BAL_QD_SUM, 25,0)
	FROM TBL_ACCOUNT_PLANNING_FINANCE_RESULT AS A, #AVG_LOAN_SECURED_AP AS B
	WHERE A.CIF=B.CIF AND B.YEARMONTH=''2016'+@SMONTH+''' AND ORDER_NUM = ''5'''
	EXEC(@SQL)
	SET @SQL='
	UPDATE TBL_ACCOUNT_PLANNING_FINANCE_RESULT
	SET MONTH'+@sMonths+' = STR(B.BAL_QD_SUM, 25,0)
	FROM TBL_ACCOUNT_PLANNING_FINANCE_RESULT AS A, #AVG_LOAN_UNSECURED_AP AS B
	WHERE A.CIF=B.CIF AND B.YEARMONTH=''2016'+@SMONTH+''' AND ORDER_NUM = ''6'''
	EXEC(@SQL)

	SET @SQL='
	UPDATE TBL_ACCOUNT_PLANNING_FINANCE_RESULT
	SET MONTH'+@sMonths+' = STR(B.BAL_QD_SUM, 25,0)
	FROM TBL_ACCOUNT_PLANNING_FINANCE_RESULT AS A, #BALANCE_DEPOSIT_SUM AS B
	WHERE A.CIF=B.CIF AND B.YEARMONTH=''2016'+@SMONTH+''' AND ORDER_NUM = ''7'''
	EXEC(@SQL)
	SET @SQL='
	UPDATE TBL_ACCOUNT_PLANNING_FINANCE_RESULT
	SET MONTH'+@sMonths+' = STR(B.BAL_QD_SUM, 25,0)
	FROM TBL_ACCOUNT_PLANNING_FINANCE_RESULT AS A, #BALANCE_DEPOSIT_CASA_SUM AS B
	WHERE A.CIF=B.CIF AND B.YEARMONTH=''2016'+@SMONTH+''' AND ORDER_NUM = ''8'''
	EXEC(@SQL)
	SET @SQL='
	UPDATE TBL_ACCOUNT_PLANNING_FINANCE_RESULT
	SET MONTH'+@sMonths+' = STR(B.BAL_QD_SUM, 25,0)
	FROM TBL_ACCOUNT_PLANNING_FINANCE_RESULT AS A, #BALANCE_DEPOSIT_KY_HAN_SUM AS B
	WHERE A.CIF=B.CIF AND B.YEARMONTH=''2016'+@SMONTH+''' AND ORDER_NUM = ''9'''
	EXEC(@SQL)
	
	SET @SQL='
	UPDATE TBL_ACCOUNT_PLANNING_FINANCE_RESULT
	SET MONTH'+@sMonths+' = STR(B.BAL_QD_SUM, 25,0)
	FROM TBL_ACCOUNT_PLANNING_FINANCE_RESULT AS A, #AVG_DEPOSIT_SUM_AP AS B
	WHERE A.CIF=B.CIF AND B.YEARMONTH=''2016'+@SMONTH+''' AND ORDER_NUM = ''10'''
	EXEC(@SQL)
	SET @SQL='
	UPDATE TBL_ACCOUNT_PLANNING_FINANCE_RESULT
	SET MONTH'+@sMonths+' = STR(B.BAL_QD_SUM, 25,0)
	FROM TBL_ACCOUNT_PLANNING_FINANCE_RESULT AS A, #AVG_DEPOSIT_CASA_SUM_AP AS B
	WHERE A.CIF=B.CIF AND B.YEARMONTH=''2016'+@SMONTH+''' AND ORDER_NUM = ''11'''
	EXEC(@SQL)
	SET @SQL='
	UPDATE TBL_ACCOUNT_PLANNING_FINANCE_RESULT
	SET MONTH'+@sMonths+' = STR(B.BAL_QD_SUM, 25,0)
	FROM TBL_ACCOUNT_PLANNING_FINANCE_RESULT AS A, #AVG_DEPOSIT_KY_HAN_SUM_AP AS B
	WHERE A.CIF=B.CIF AND B.YEARMONTH=''2016'+@SMONTH+''' AND ORDER_NUM = ''12'''
	EXEC(@SQL)
	SET @INTMONTH = @INTMONTH + 1
END

--SELECT TOP 1 * FROM [SERVER74].[BICDATA].[dbo].[LNTB_DISBURSEMENT]


--SELECT BUSINESS_DATE, YEARMONTH FROM LNTB_DISBURSEMENT_2016


PRINT ('3.CREATE DATA DOANH SO')

DECLARE @DATE_UPDATE NVARCHAR(100)= (SELECT [DATE_UPDATE] FROM [DATE_EM_REPORT])

INSERT INTO LNTB_DISBURSEMENT_2016
SELECT * FROM [SERVER74].[BICDATA].[dbo].[LNTB_DISBURSEMENT]
WHERE BUSINESS_DATE > (SELECT MAX(BUSINESS_DATE) FROM LNTB_DISBURSEMENT_2016) AND BUSINESS_DATE <= @DATE_UPDATE

delete from LNTB_DISBURSEMENT_ACCOUNT_PLANNING

	INSERT INTO LNTB_DISBURSEMENT_ACCOUNT_PLANNING
	SELECT *,CONVERT(NVARCHAR(6),BUSINESS_DATE,112) FROM LNTB_DISBURSEMENT_2016
		WHERE CIF IN (SELECT CIF FROM TBL_ACCOUNT_PLANNING)

IF OBJECT_ID('TEMPDB.DBO.#DISBURSEMENT_SUM_AP') IS NOT NULL
	  DROP TABLE #DISBURSEMENT_SUM_AP

	SELECT SUM(CONVERT(FLOAT, BAL_QD)) AS BAL_QD_SUM,YEARMONTH, CIF  INTO #DISBURSEMENT_SUM_AP
	FROM LNTB_DISBURSEMENT_ACCOUNT_PLANNING 
	GROUP BY CIF, YEARMONTH
	
	IF OBJECT_ID('TEMPDB.DBO.#MASTER_SUM') IS NOT NULL
	  DROP TABLE #MASTER_SUM 
	SELECT SUM(CONVERT(FLOAT, BAL_QD)) AS BAL_QD_SUM,YEARMONTH, CIF  INTO #MASTER_SUM
			FROM DPTB_MASTER_ACCOUNT_PLANNING_2016 
			WHERE BAL_QD IS NOT NULL
			GROUP BY CIF, YEARMONTH

	IF OBJECT_ID('TEMPDB.DBO.#FT_QT_SUM') IS NOT NULL
	  DROP TABLE #FT_QT_SUM 
	SELECT SUM(CONVERT(FLOAT, AMOUNT_QD)) AS SALE_QD, CIF, YEARMONTH INTO #FT_QT_SUM
			FROM FT_HIST_CHUYEN_TIEN_QT 
			GROUP BY CIF, YEARMONTH

	IF OBJECT_ID('TEMPDB.DBO.#AMOUNT_LC_SUM') IS NOT NULL
	  DROP TABLE #AMOUNT_LC_SUM 
	SELECT SUM(CONVERT(FLOAT, AMOUNT_QD)) AS SALE_QD, CIF, YEARMONTH INTO #AMOUNT_LC_SUM 
		FROM DOANH_SO_LC_THEO_THANG_2016 
		GROUP BY CIF, YEARMONTH

	IF OBJECT_ID('TEMPDB.DBO.#FT_TN_SUM') IS NOT NULL
	DROP TABLE #FT_TN_SUM 
	SELECT SUM(CONVERT(FLOAT, AMOUNT_QD)) AS SALE_QD, CIF, YEARMONTH INTO #FT_TN_SUM
		FROM FT_HIST_ACCOUNT_PLANNING_DOMESTIC_TRANSFER 
		GROUP BY CIF, YEARMONTH


	IF OBJECT_ID('TEMPDB.DBO.#FX_FO_SUM') IS NOT NULL
	DROP TABLE #FX_FO_SUM 
	SELECT SUM(CONVERT(FLOAT, [BASE AMOUNT])) AS SALE_QD, CIF, YEARMONTH INTO #FX_FO_SUM
		FROM ACCOUNT_PLANNING_FX_FO 
		GROUP BY CIF, YEARMONTH
		
	IF OBJECT_ID('TEMPDB.DBO.#BL_SUM') IS NOT NULL
	DROP TABLE #BL_SUM
	SELECT SUM(CONVERT(FLOAT, ORG_AMOUNT)) AS SALE_QD, CUST_ID AS  CIF, YEARMONTH INTO #BL_SUM
		FROM ACCOUNT_PLANNING_BL 
		GROUP BY CUST_ID, YEARMONTH

		--SELECT * FROM #BL_SUM
PRINT('UPDATE DOANH SO')
--DECLARE @INTMONTH INT
--SET @INTMONTH = 1
--DECLARE @SMONTH NVARCHAR(20)
--DECLARE @SQL NVARCHAR(MAX)
SET @INTMONTH = 1
WHILE @INTMONTH <=@NOWMONTH
BEGIN
	SET @SMONTH= CAST(@INTMONTH AS NVARCHAR(20))
	
	set @sMonths = @sMonth

	if(@INTMONTH <10) set @SMONTH = '0' + @SMONTH

	SET @SQL='
	UPDATE TBL_ACCOUNT_PLANNING_FINANCE_RESULT
	SET MONTH'+@sMonths+' = STR(B.BAL_QD_SUM, 25,0)
	FROM TBL_ACCOUNT_PLANNING_FINANCE_RESULT AS A, #DISBURSEMENT_SUM_AP AS B
	WHERE A.CIF=B.CIF AND B.YEARMONTH=''2016'+@SMONTH+''' AND ORDER_NUM = ''27'''
	EXEC(@SQL)

	SET @SQL='
	UPDATE TBL_ACCOUNT_PLANNING_FINANCE_RESULT
	SET MONTH'+@sMonths+' = STR(B.BAL_QD_SUM, 25,0)
	FROM TBL_ACCOUNT_PLANNING_FINANCE_RESULT AS A, #MASTER_SUM AS B
	WHERE A.CIF=B.CIF AND B.YEARMONTH=''2016'+@SMONTH+''' AND ORDER_NUM = ''28'''
	EXEC(@SQL)
	
	SET @SQL='
	UPDATE TBL_ACCOUNT_PLANNING_FINANCE_RESULT
	SET MONTH'+@sMonths+' = STR(B.SALE_QD, 25,0)
	FROM TBL_ACCOUNT_PLANNING_FINANCE_RESULT AS A, #BL_SUM AS B
	WHERE A.CIF=B.CIF AND B.YEARMONTH=''2016'+@SMONTH+''' AND ORDER_NUM = ''29'''
	EXEC(@SQL)

	SET @SQL='
	UPDATE TBL_ACCOUNT_PLANNING_FINANCE_RESULT
	SET MONTH'+@sMonths+' = STR(B.SALE_QD, 25,0)
	FROM TBL_ACCOUNT_PLANNING_FINANCE_RESULT AS A,#AMOUNT_LC_SUM AS B
	WHERE A.CIF=B.CIF AND B.YEARMONTH=''2016'+@SMONTH+''' AND ORDER_NUM = ''30'''
	EXEC(@SQL)

	SET @SQL='
	UPDATE TBL_ACCOUNT_PLANNING_FINANCE_RESULT
	SET MONTH'+@sMonths+' = STR(B.SALE_QD, 25,0)
	FROM TBL_ACCOUNT_PLANNING_FINANCE_RESULT AS A, #FT_QT_SUM AS B
	WHERE A.CIF=B.CIF AND B.YEARMONTH=''2016'+@SMONTH+''' AND ORDER_NUM = ''31'''
	EXEC(@SQL)

	SET @SQL='
	UPDATE TBL_ACCOUNT_PLANNING_FINANCE_RESULT
	SET MONTH'+@sMonths+' = STR(B.SALE_QD, 25,0)
	FROM TBL_ACCOUNT_PLANNING_FINANCE_RESULT AS A, #FT_TN_SUM AS B
	WHERE A.CIF=B.CIF AND B.YEARMONTH=''2016'+@SMONTH+''' AND ORDER_NUM = ''32'''
	EXEC(@SQL)
	
	SET @SQL='
	UPDATE TBL_ACCOUNT_PLANNING_FINANCE_RESULT
	SET MONTH'+@sMonths+' = STR(B.SALE_QD, 25,0)
	FROM TBL_ACCOUNT_PLANNING_FINANCE_RESULT AS A, #FX_FO_SUM AS B
	WHERE A.CIF=B.CIF AND B.YEARMONTH=''2016'+@SMONTH+''' AND ORDER_NUM = ''33'''
	EXEC(@SQL)
	SET @INTMONTH = @INTMONTH + 1
END

PRINT('4.UPDATE THU NHAP THUAN')
--DECLARE @INTMONTH INT
--SET @INTMONTH = 1
--DECLARE @SMONTH NVARCHAR(20)
--DECLARE @SQL NVARCHAR(MAX)
SET @INTMONTH = 1
WHILE @INTMONTH <= @NOWMONTH
BEGIN
	SET @SMONTH= CAST(@INTMONTH AS NVARCHAR(20))

	SET @SQL='
	UPDATE TBL_ACCOUNT_PLANNING_FINANCE_RESULT
	SET MONTH'+@SMONTH+' = STR(B.TOI, 25,0)
	FROM TBL_ACCOUNT_PLANNING_FINANCE_RESULT AS A,NFI_TOI_ACCOUNT_PLANNING_SUM AS B
	WHERE A.CIF COLLATE DATABASE_DEFAULT =B.CIF AND B.YEARMONTH='''+@SMONTH+''' AND ORDER_NUM = ''19'''
	EXEC(@SQL)
	SET @SQL='
	UPDATE TBL_ACCOUNT_PLANNING_FINANCE_RESULT
	SET MONTH'+@SMONTH+' = STR(B.NII, 25,0)
	FROM TBL_ACCOUNT_PLANNING_FINANCE_RESULT AS A,NFI_TOI_ACCOUNT_PLANNING_SUM AS B
	WHERE A.CIF COLLATE DATABASE_DEFAULT =B.CIF AND B.YEARMONTH='''+@SMONTH+''' AND ORDER_NUM = ''20'''
	EXEC(@SQL)
	SET @SQL='
	UPDATE TBL_ACCOUNT_PLANNING_FINANCE_RESULT
	SET MONTH'+@SMONTH+' = STR(B.NFI, 25,0)
	FROM TBL_ACCOUNT_PLANNING_FINANCE_RESULT AS A,NFI_TOI_ACCOUNT_PLANNING_SUM AS B
	WHERE A.CIF COLLATE DATABASE_DEFAULT =B.CIF AND B.YEARMONTH='''+@SMONTH+''' AND ORDER_NUM = ''21'''
	EXEC(@SQL)
	SET @SQL='
	UPDATE TBL_ACCOUNT_PLANNING_FINANCE_RESULT
	SET MONTH'+@SMONTH+' = STR(B.FEX, 25,0)
	FROM TBL_ACCOUNT_PLANNING_FINANCE_RESULT AS A,NFI_TOI_ACCOUNT_PLANNING_SUM AS B
	WHERE A.CIF COLLATE DATABASE_DEFAULT =B.CIF AND B.YEARMONTH='''+@SMONTH+''' AND ORDER_NUM = ''22'''
	EXEC(@SQL)
	SET @INTMONTH = @INTMONTH + 1
END

--DECLARE @INTMONTH INT
--DECLARE @NOWMONTH INT
--SET @INTMONTH = 1
--SET @NOWMONTH = 6
--DECLARE @SMONTH NVARCHAR(20)
--DECLARE @SQL NVARCHAR(MAX)
SET @SMONTH= CAST(@NOWMONTH AS NVARCHAR(20))
SET @SQL='UPDATE TBL_ACCOUNT_PLANNING_FINANCE_RESULT
	SET YEAR= MONTH'+@SMONTH+'
	WHERE ORDER_NUM IN (''1'',''2'',''3'',''7'',''8'',''9'',''23'',''24'',''25'',''26'')'
EXEC(@SQL)

SET @SQL='UPDATE TBL_ACCOUNT_PLANNING_FINANCE_RESULT
	SET YEAR= STR((CONVERT(FLOAT,MONTH1) + CONVERT(FLOAT,MONTH2) + CONVERT(FLOAT,MONTH3) + CONVERT(FLOAT,MONTH4)
			+ CONVERT(FLOAT,MONTH5) + CONVERT(FLOAT,MONTH6) + CONVERT(FLOAT,MONTH7) + CONVERT(FLOAT,MONTH8)
			+ CONVERT(FLOAT,MONTH9) + CONVERT(FLOAT,MONTH10) + CONVERT(FLOAT,MONTH11) + CONVERT(FLOAT,MONTH12)),25,0)
	WHERE ORDER_NUM IN (''19'',''20'',''21'',''22'',''27'',''28'',''29'',''30'',''31'',''32'',''33'')'
EXEC(@SQL)
	
SET @SQL='UPDATE TBL_ACCOUNT_PLANNING_FINANCE_RESULT
	SET YEAR= STR((CONVERT(FLOAT,MONTH1) + CONVERT(FLOAT,MONTH2) + CONVERT(FLOAT,MONTH3) + CONVERT(FLOAT,MONTH4)
			+ CONVERT(FLOAT,MONTH5) + CONVERT(FLOAT,MONTH6) + CONVERT(FLOAT,MONTH7) + CONVERT(FLOAT,MONTH8)
			+ CONVERT(FLOAT,MONTH9) + CONVERT(FLOAT,MONTH10) + CONVERT(FLOAT,MONTH11) + CONVERT(FLOAT,MONTH12))/6,25,0)
	WHERE ORDER_NUM IN (''4'',''5'',''6'',''10'',''11'',''12'')'
EXEC(@SQL)